import time

import requests
import json
from db import busDictionary
import pymysql
# 创建到数据库的连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='123456',
    database='bus_view'
)
# 创建一个新的游标对象
cursor = conn.cursor()

url = 'http://zngj.xngjbus.com:8081/app/v2/bus/new/line'
cursor.execute('TRUNCATE TABLE bus_station')
cursor.execute('TRUNCATE TABLE station_details')
cursor.execute('TRUNCATE TABLE bus_count')
print("自动爬取程序已启动...")
for key,values in busDictionary.busbook.items():
    print("正在抓取"+key+"路车信息")
    data = {
        "deviceType": "2",
        "versionCode": "302",
        "lineCode": values,
        "appKey": "3296B34AC79F5010"
    }
    headers = {
        'User-Agent': 'Apifox/1.0.0 (https://apifox.com)',
        'Content-Type': 'application/json',
        'Accept': '*/*',
        'Host': 'zngj.xngjbus.com:8081',
        'Connection': 'keep-alive'
    }
    response = requests.post(url, data=json.dumps(data), headers=headers)
    response_data = response.json()
    for item in response_data["data"]["lineBusDtoList"]:
        licence_plate = item["busNo"]
        cursor.execute(f'INSERT IGNORE INTO bus_details(licence_plate, bus_num) VALUES ("{licence_plate}","{key}");')
    price = response_data["data"]["lineDetailBaseDto"]["price"]
    for item in response_data["data"]["stopDetailList"]:
        stop_name = item["stopName"]
        latitude = item["latitude"]
        longitude = item["longitude"]
        # 执行一个SQL插入
        cursor.execute(f'INSERT INTO bus_station(bus_num, station_name) VALUES ("{key}","{stop_name}");')
        cursor.execute(f'INSERT INTO station_details(station_name,longitude,latitude) VALUES ("{stop_name}","{longitude}","{latitude}") ON DUPLICATE KEY UPDATE count = count + 1;')
        cursor.execute(f'INSERT INTO bus_count(bus_num,bus_price,bus_count) VALUES ("{key}","{price}",(select count(*) from bus_details where bus_num="{key}")) ON DUPLICATE KEY UPDATE station_count = station_count + 1;')
        # print(item["stopName"])
    conn.commit()
    time.sleep(0.5)
# 关闭游标和连接
cursor.close()
conn.close()